package com.hyving.home.jdbcdatasource.repository.dao;

import com.alibaba.fastjson.JSON;
import com.hyving.home.jdbcdatasource.repository.entity.Book;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

@Repository
public class SimpleRepository {

    @Autowired
    private JdbcTemplate jdbcTemplate;


    public void createTable(){

        String sql = "CREATE TABLE `ocean`.`book`  (" +
                "  `id` int(0) NOT NULL AUTO_INCREMENT," +
                "  `name` varchar(255) NULL," +
                "  `price` decimal(10, 2) NULL," +
                "  PRIMARY KEY (`id`)" +
                ");";

        jdbcTemplate.execute(sql);
    }

    public void dropTable(){

        String sql = "drop TABLE `ocean`.`book`;";

        jdbcTemplate.execute(sql);
    }

    public int insertBook(String name, Double price){

        String sql = "INSERT INTO `ocean`.`book`( `name`, `price`) VALUES (?, ?)";

        return jdbcTemplate.update(sql,name,price);
    }

    public int updateBook(Book book){
        String sql ="UPDATE `ocean`.`book` SET `name` = ?, `price` = ? WHERE `id` = ?";
        return jdbcTemplate.update(sql,book.getName(),book.getPrice(),book.getId());

    }

    public int deleteBook(Integer id){
        String sql = "delete from `ocean`.`book` where `id` =?";
        return jdbcTemplate.update(sql,id);
    }

    public String queryString(){
        String sql ="select name from `ocean`.`book` limit 1";
        return jdbcTemplate.queryForObject(sql,String.class);
    }

    public Book queryBook(){
        String sql ="select * from `ocean`.`book` limit 1 ";

        RowMapper<Book> rowMapper=new BeanPropertyRowMapper<Book>(Book.class);
        Book user= jdbcTemplate.queryForObject(sql, rowMapper);
        return user;
    }

    public void queryMap(){
        String sql ="select * from `ocean`.`book` limit 1";
        Map<String, Object> map =jdbcTemplate.queryForMap(sql);
        System.out.println(JSON.toJSONString(map));
    }

    public void queryList(){
        String sql ="select * from `ocean`.`book`  ";
        List<Map<String, Object>> list =jdbcTemplate.queryForList(sql);
        System.out.println(JSON.toJSONString(list));

    }

    public void queryRowMapper(){
        String sql ="select * from `ocean`.`book` ";
        List<Book> list =jdbcTemplate.query(sql,new RowMapper<Book>() {
                    @Override
                    public Book mapRow(ResultSet arg0, int arg1) throws SQLException {
                        Book p = new Book();
                        p.setId(arg0.getInt("id"));
                        p.setName(arg0.getString("name"));
                        p.setPrice(arg0.getDouble("price"));
                        return p;
                    }
                });
        System.out.println(JSON.toJSONString(list));



    }







}
